package com.xbongbong.pro.statistics.help;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xbongbong.paas.constant.BiConstant;
import com.xbongbong.paas.constant.PaasConstant;
import com.xbongbong.paas.constant.XbbConstant;
import com.xbongbong.paas.elasticsearch.util.EsUtil;
import com.xbongbong.paas.enums.AggTypeEnum;
import com.xbongbong.paas.enums.DateDiffTypeEnum;
import com.xbongbong.paas.enums.FieldTypeEnum;
import com.xbongbong.paas.enums.bi.XbbTableRefEnum;
import com.xbongbong.paas.toolbox.exception.XbbException;
import com.xbongbong.paas.toolbox.util.CollectionsUtil;
import com.xbongbong.paas.toolbox.util.RegexUtil;
import com.xbongbong.paas.toolbox.util.StringUtil;
import com.xbongbong.pro.bi.commons.constant.Constant;
import com.xbongbong.pro.bi.commons.pojo.AggFieldPojo;
import com.xbongbong.pro.bi.commons.pojo.SubSqlParamPojo;
import com.xbongbong.pro.domain.entity.ChartEntity;
import com.xbongbong.pro.enums.errorcodes.BiErrorCodeEnum;
import com.xbongbong.pro.statistic.constant.RelatedProductConstant;
import com.xbongbong.pro.statistic.enums.AdvancedComputingFirstEnum;
import com.xbongbong.pro.statistic.enums.ChartTypeEnum;
import com.xbongbong.pro.statistic.result.pojo.FieldAttrPojo;
import com.xbongbong.pro.statistics.enums.BiMathEnum;
import com.xbongbong.saas.constant.BasicConstant;
import com.xbongbong.saas.constant.StringConstant;
import com.xbongbong.saas.constant.TabConstant;
import com.xbongbong.saas.enums.SelectProductEnum;
import com.xbongbong.saas.enums.business.ContractPerformanceEnum;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.Stack;
import java.util.function.Predicate;
import java.util.regex.Matcher;

/**
 * 计算字段处理
 * @ClassName ComputedHelp
 * @Author 梁鲁江
 * 创建时间 2021/12/13 20:39
 * 修改时间  2021/12/13 20:39
 **/
@Component
public class ComputedHelp {

    private static final Logger logger = LoggerFactory.getLogger(ComputedHelp.class);

    @Resource
    private InitSqlHelper initSqlHelper;

    public  String getString(JSONObject formulaInfo,HashMap<String, Set<String>> selectItemMap,String formula, Map<Long, String> formIdTableMap) throws XbbException {
        String symbol = RegexUtil.extractFormulaName(formula);
        // 公司涉及到的参数list
        List<String> fieldList = new ArrayList<>();
        if(Objects.equals(EsUtil.DATE_DIFF,symbol)){
            if (selectItemMap != null){
                JSONObject fields = formulaInfo.getJSONObject("fields");
                Iterator<String> iterator = fields.keySet().iterator();
                while (iterator.hasNext()){
                    String formId = iterator.next();
                    Object object = fields.get(formId);
                    List<String> fileds = (List)object;
                    fileds.stream().filter(e->e.contains("addTime") || e.contains("updateTime")).forEach(e -> InitSqlHelper.getInstance().addSelectCol(formIdTableMap, selectItemMap, Long.valueOf(formId),
                            InitSqlHelper.getInstance().generatargMax(StringUtil.getDataBaseString(e))));
                    addArgMax4WorkOrderV2(fileds, formIdTableMap, selectItemMap, formId);
                }
            }
            formula = getScriptString4Date(formula, formIdTableMap);
        }else {
            formula = formulaTranslate(formula,fieldList,formIdTableMap);
            if (Objects.nonNull(formIdTableMap) && formIdTableMap.containsValue(XbbTableRefEnum.WORK_ORDER_V2_NODE_LOG_INFO.getTableName())) {
                InitSqlHelper.getInstance().addSelectCol(formIdTableMap, selectItemMap, Constant.nodeLogInfoFormId,
                        InitSqlHelper.getInstance().generatargMax(StringUtil.getDataBaseString("stayTime")));
            }
        }
        return formula;
    }

    /**
     * 日期的计算字段，需要在内层查询到(argMax(...)这个)，而nodeLogInfo表的日期函数在data外
     * @param fileds
     * @param formIdTableMap
     * @param selectItemMap
     * @param formId
     */
    private void addArgMax4WorkOrderV2(List<String> fileds, Map<Long, String> formIdTableMap, HashMap<String, Set<String>> selectItemMap, String formId) {
        if (MapUtils.isEmpty(formIdTableMap)) {
            return;
        }
        for (String tableName : formIdTableMap.values()) {
            //业务类型判断
            if (Objects.equals(tableName, XbbTableRefEnum.WORK_ORDER_V2_NODE_LOG_INFO.getTableName())) {
                fileds.stream().filter(getStringPredicate()).forEach(e -> InitSqlHelper.getInstance().addSelectCol(formIdTableMap, selectItemMap, Long.valueOf(formId),
                        InitSqlHelper.getInstance().generatargMax(StringUtil.getDataBaseString(e))));
            }
        }
    }

    /**
     * 新工单-nodeloginfo表的日期字段
     * @return
     */
    private Predicate<String> getStringPredicate() {
        return e -> e.contains("startTime") || e.contains("startSignTime") || e.contains("finishSignBackTime")
                || e.contains("acceptInTime") || e.contains("startInTime") || e.contains("finishInTime");
    }

    
    /**
     * 日期公式
     * @param formula
     * @param formIdTableMap
     * @return
     * @throws XbbException
     */
    public String getScriptString4Date(String formula, Map<Long, String> formIdTableMap) throws XbbException {
        //最外层公式的大写英文
        String formulaName = BiMathEnum.DATE_DIFF.getFormula();
        //最外层公式的变量数组,将最外层的全部变量赋值给fields
        String fields = formula.substring(formulaName.length()).replaceAll("\\(", "").replaceAll("\\)", "");
        String[] fieldsArray = fields.split(PaasConstant.COMMAS);
        List<String> fieldsList = new ArrayList<>(Arrays.asList(fieldsArray));

        if(MapUtils.isEmpty(formIdTableMap)){
            //数据集图表调用时formIdTableMap恒为null，其余数据源恒不为null，因此这里做逻辑拆分
            return getDateDiffByDs(fieldsList);
        }

        //第1个值
        String firstField = translateBaseSql(formIdTableMap, fieldsList.get(BasicConstant.ZERO));
        String fField = dealStringField(firstField);
        //第2个值
        String secondField = translateBaseSql(formIdTableMap, fieldsList.get(BasicConstant.ONE));
        String sField = dealStringField(secondField);

        StringBuffer stringBuffer = new StringBuffer();
        String unit = DateDiffTypeEnum.DAY.getAlias();
        if(fieldsList.size() == 3){
            unit = fieldsList.get(2).replaceAll("\"","");
        }
        DateDiffTypeEnum typeEnum = DateDiffTypeEnum.getByAlias(unit);
        stringBuffer.append(XbbConstant.DATE_DIFF).append(XbbConstant.L_P)
                .append(typeEnum.getArithmetic()).append(PaasConstant.COMMAS).append(InitSqlHelper.getInstance().renameUpdateTimeWithPre(fField))
                .append(PaasConstant.COMMAS).append(InitSqlHelper.getInstance().renameUpdateTimeWithPre(sField))
                .append(XbbConstant.R_P);
        return stringBuffer.toString();
    }

    /**
     * 处理Now函数，拼接上括号还有就是处理成时间函数展示的格式
     * @param field 待拼接字段
     * @return 拼接好的字段
     */
    private String dealStringField(String field) {
        if (Objects.equals(BiMathEnum.NOW.getFormula(), field)){
            field = XbbConstant.L_P + BiMathEnum.NOW.getArithmetic() + XbbConstant.R_P;
        }
        String fField = XbbConstant.TO_DATE_TIME + field;
        if(fField.contains("data") && !fField.contains(XbbTableRefEnum.WORK_ORDER_V2.getTableName())
                && !fField.contains("add_time") && !fField.contains("update_time")){
            fField = "if(" + field.replace(XbbConstant.JSON_EXTRACT_DOUBLE,XbbConstant.JSON_HAS) + "," + fField  + ",null)";
        }
        return fField;
    }

    /**
     * 获取数据集使用的 日期函数sql
     *
     * @param fieldsList 解析后的公式数组
     * @return java.lang.String
     * @author zyy
     * @date 2022/10/26
     * @version v1.0
     */
    private String getDateDiffByDs(List<String> fieldsList) {
        if(CollectionUtils.isEmpty(fieldsList)){
            return "";
        }
        //获取使用的函数
        String prefixFunction = DateDiffTypeEnum.matchKeyWord(fieldsList);
        //拼接函数
        StringBuilder stringBuilder = new StringBuilder();
        String fField;
        String firstField = translateBaseSql(null, fieldsList.get(BasicConstant.ZERO));
        // NOW函数
        if (Objects.equals(BiMathEnum.NOW.getFormula(), firstField)) {
            fField = BiMathEnum.NOW.getArithmetic();
        } else {
            fField = XbbConstant.FROM_UNIXTIME + XbbConstant.L_P + firstField + XbbConstant.R_P;
        }

        String sField;
        String secondField = translateBaseSql(null, fieldsList.get(BasicConstant.ONE));
        // NOW函数
        if (Objects.equals(BiMathEnum.NOW.getFormula(), secondField)) {
            sField = BiMathEnum.NOW.getArithmetic();
        } else {
            sField = XbbConstant.FROM_UNIXTIME + XbbConstant.L_P + secondField + XbbConstant.R_P;
        }

        stringBuilder
                .append(prefixFunction).append(XbbConstant.L_P)
                .append(fField)
                .append(XbbConstant.SQL_COMMA)
                .append(sField)
                .append(XbbConstant.R_P);

        return stringBuilder.toString();
    }

    public  String formulaTranslate(String textString, List<String> fieldList,Map<Long, String> formIdTableMap) throws XbbException {
        //最终拼接的公式
        StringBuilder textFormulaString = new StringBuilder(XbbConstant.L_P);
        //最外层公式的大写英文
        String formulaName = RegexUtil.extractFormulaName(textString);
        if(StringUtils.isBlank(formulaName)){
            String defaultAggName = AggTypeEnum.SUM.getAlias().toUpperCase();
            //防止只有单独的公式，没有聚合前缀
            formulaName = defaultAggName;
            textString = defaultAggName + XbbConstant.L_P+ textString + XbbConstant.R_P;
        }
        //解析计算字段
        List<String> fieldsList = new ArrayList<>();
        analysisFormula(fieldsList,textString);
        //nodeLogInfo特殊逻辑
        fieldsList = getFieldsList4WorkOrderV2(fieldsList, formIdTableMap, textString, formulaName);
        //初始化除数
        String divisorStr = "";
        boolean divideNestedFlag = Objects.equals(formulaName,"DIVIDES");
        if ("IF".equals(formulaName)) {
            if((fieldsList.size() != 5) || !RegexUtil.checkLogicalSymbol.matcher(fieldsList.get(1)).matches()){
                throw new XbbException(BiErrorCodeEnum.API_ERROR_241017,BiErrorCodeEnum.API_ERROR_241017.getMsg());
            }
            String preString = "";
            String symbol = "";
            String sufString = "";
            String field1 = "";
            String field2 = "";
            for (int i = 0; i < fieldsList.size(); i++) {
                String s = fieldsList.get(i);
                switch (i) {
                    case 0:
                        List<String> tempList = new ArrayList<>();
                        preString = RegexUtil.checkFormulaField.matcher(s).matches() ? formulaTranslate(s,tempList,formIdTableMap) : translateBaseSql(formIdTableMap, s);
                        if (CollectionsUtil.isNotEmpty(tempList)){
                            fieldList.addAll(tempList);
                        }else {
                            if (!RegexUtil.isNumber(s)){
                                fieldList.add(s);
                            }
                        }
                        break;
                    case 1:
                        symbol = s;
                        break;
                    case 2:
                        List<String> tempList2 = new ArrayList<>();
                        sufString = RegexUtil.checkFormulaField.matcher(s).matches() ? formulaTranslate(s,tempList2,formIdTableMap) : translateBaseSql(formIdTableMap, s);
                        if (CollectionsUtil.isNotEmpty(tempList2)){
                            fieldList.addAll(tempList2);
                        }else {
                            if (!RegexUtil.isNumber(s)){
                                fieldList.add(s);
                            }
                        }
                        break;
                    case 3:
                        List<String> tempList3 = new ArrayList<>();
                        field1 = RegexUtil.checkFormulaField.matcher(s).matches() ? formulaTranslate(s,tempList3,formIdTableMap) : translateBaseSql(formIdTableMap, s);
                        if (CollectionsUtil.isNotEmpty(tempList3)){
                            fieldList.addAll(tempList3);
                        }else {
                            if (!RegexUtil.isNumber(s)){
                                fieldList.add(s);
                            }
                        }
                        break;
                    case 4:
                        List<String> tempList4 = new ArrayList<>();
                        field2 = RegexUtil.checkFormulaField.matcher(s).matches() ? formulaTranslate(s,tempList4,formIdTableMap) : translateBaseSql(formIdTableMap, s);
                        if (CollectionsUtil.isNotEmpty(tempList4)){
                            fieldList.addAll(tempList4);
                        }else {
                            if (!RegexUtil.isNumber(s)){
                                fieldList.add(s);
                            }
                        }
                        break;
                    default:
                        break;
                }
            }
            textFormulaString.append(BiMathEnum.IF.getArithmetic()).append(XbbConstant.L_P).append(preString + symbol + sufString + "," + field1 + "," + field2).append(XbbConstant.R_P);
        } else if ("SUMIF".equals(formulaName)) {
            if(fieldsList.size() < 3 || !RegexUtil.checkLogicalSymbol.matcher(fieldsList.get(fieldsList.size()-2)).matches()){
                throw new XbbException(BiErrorCodeEnum.API_ERROR_241017, BiErrorCodeEnum.API_ERROR_241017.getMsg());
            }
            StringBuilder logicalSymbol = new StringBuilder();
            StringBuilder tempScript = new StringBuilder("(");
            List<String> paramsList = new ArrayList<>();
            for (int i = 0; i < fieldsList.size(); i++) {
                String s = fieldsList.get(i);
                if (RegexUtil.checkFormulaField.matcher(s).matches()) {
                    s = formulaTranslate(s,new ArrayList<>(),formIdTableMap);
                }
                if (RegexUtil.checkLogicalSymbol.matcher(s).matches()) {
                    logicalSymbol.append(s);
                } else {
                    if (logicalSymbol.toString().length() == 0) {
                        paramsList.add(s);
                    }
                }
                if (i == fieldsList.size() - 1) {
                    logicalSymbol.append(s);
                }
            }
            for (int i = 0; i < paramsList.size(); i++) {
                String s = paramsList.get(i);
                //如果s是数字，
                if (!(s.contains("num") || s.contains("date"))){
                    throw new XbbException(BiErrorCodeEnum.API_ERROR_241017,BiErrorCodeEnum.API_ERROR_241017.getMsg());
                }
                if (i == (paramsList.size() - 1)) {
                    tempScript.append(s).append(logicalSymbol).append('?').append(s).append(":0");
                } else {
                    tempScript.append(s).append(logicalSymbol).append("?").append(s).append(":0").append("+");
                }
            }
            tempScript.append(')');
//            textFormulaString = tempScript;
        } else if(XbbConstant.COUNT.equals(formulaName) || XbbConstant.COUNTD.equals(formulaName)){
            //目前count不允许多层嵌套，这里直接取第一个公式
            String s = fieldsList.get(BasicConstant.ZERO);
            if(XbbConstant.COUNTD.equals(formulaName)){
                textFormulaString.append(XbbConstant.DISTINCT).append(XbbConstant.SPACE);
            }
            if(!s.contains(XbbConstant.JSON_EXTRACT_DOUBLE)){
                //负责人字段需要替换名称
                // ck = ownerId-> team_main
                // dataset = ownerId_x -> myTeamMain_x
                s = replaceOwnerId(s,formIdTableMap);
                //解析时提前标注使用了count函数
                String tmpStr = s + XbbConstant.COUNT;
                translateBaseSql(formIdTableMap, textFormulaString, tmpStr);
            }else {
                textFormulaString.append(s);
            }
            return textFormulaString + XbbConstant.R_P;
        }else {
            for (int i = 0; i < fieldsList.size(); i++) {
                String s = fieldsList.get(i);
                //先判断变量是不是公式
                List<String> tempList = new ArrayList<>();
                if (RegexUtil.checkFormulaField.matcher(s).matches() || RegexUtil.checkFormulaDateField.matcher(s).matches()) {
                    s = formulaTranslate(s,tempList,formIdTableMap);
                    if (CollectionsUtil.isNotEmpty(tempList)){
                        fieldList.addAll(tempList);
                    }
                }
                if (CollectionsUtil.isEmpty(tempList)){
                    if (!RegexUtil.isNumber(s)){
                        fieldList.add(s);
                    }
                }
                try {
                    if(divideNestedFlag && (i == (fieldsList.size() - 1))){
                        //divideNestedFlag 为true时，只会有两个字段
                        divisorStr = getDivisorStr(formIdTableMap,divisorStr,s);
                    }
                    if(!s.contains(XbbConstant.JSON_EXTRACT_DOUBLE)){
                        translateBaseSql(formIdTableMap, textFormulaString, s);
                    }else {
                        textFormulaString.append(s);
                    }
                    String arithmetic = BiMathEnum.getArithmetic4Formula(formulaName);
                    if(StringUtil.isEmpty(arithmetic)){
                        throw new XbbException(BiErrorCodeEnum.API_ERROR_241017,BiErrorCodeEnum.API_ERROR_241017.getMsg());
                    }
                    textFormulaString.append(arithmetic);
                } catch (XbbException e) {
                    throw new XbbException(BiErrorCodeEnum.API_ERROR_241017,BiErrorCodeEnum.API_ERROR_241017.getMsg());
                }
                //如果是最后一个变量
                if (i == (fieldsList.size() - 1)) {
                    textFormulaString.delete(textFormulaString.length() - 1, textFormulaString.length());
                    if ("AVERAGE".equals(formulaName)) {
                        textFormulaString.insert(0, '(');
                        textFormulaString.append(')');
                        textFormulaString.append("/" + fieldsList.size());
                    }
                }
            }
            if (Objects.equals(formulaName,"MAX") ||  Objects.equals(formulaName,"MIN")){
                StringBuilder maxOrMinFormula = new StringBuilder(XbbConstant.L_P);
                String str = "";
                if(Objects.equals(formulaName,"MAX")){
                    str = BiMathEnum.MAX.getArithmetic();
                }else if(Objects.equals(formulaName,"MIN")){
                    str = BiMathEnum.MIN.getArithmetic();
                }else {
                    str = BiMathEnum.MAX.getArithmetic();
                }
                maxOrMinFormula.append(str).append(XbbConstant.L_P);
                for (int i = 0; i < fieldsList.size(); i++) {
                    String s = fieldsList.get(i);
                    if(i != 0){
                        maxOrMinFormula.append(",");
                    }
                    //先判断变量是不是公式
                    if (RegexUtil.checkFormulaField.matcher(s).matches() || RegexUtil.checkFormulaDateField.matcher(s).matches()) {
                        s = formulaTranslate(s, new ArrayList<>(), formIdTableMap);
                    } else {
                        s = translateBaseSql(formIdTableMap, s);
                    }
                    maxOrMinFormula.append(s);
                }
                maxOrMinFormula.append(XbbConstant.R_P).append(XbbConstant.R_P);
                return maxOrMinFormula.toString();
            }
        }
        String result = textFormulaString.append(XbbConstant.R_P).toString().replaceAll("[\\[\\]]", "");
        if(divideNestedFlag){
            //除法需要进行if函数嵌套
            result = InitSqlHelper.ifFunction(divisorStr,XbbConstant.EQUAL ,StringConstant.ZERO,StringConstant.ZERO,result);
        }
        return result;
    }

    /**
     * nodeLogInfo表字段都是data外字段，逻辑特殊
     * @param fieldsList
     * @param formIdTableMap
     * @param textString
     * @param formulaName
     * @return
     */
    private List<String> getFieldsList4WorkOrderV2(List<String> fieldsList, Map<Long, String> formIdTableMap, String textString, String formulaName) {
        if (Objects.nonNull(formIdTableMap) && formIdTableMap.containsValue(XbbTableRefEnum.WORK_ORDER_V2_NODE_LOG_INFO.getTableName())
                && textString.contains("0.")) {
            // 移除字符串开头的公式name
            String s = StringUtils.removeStart(textString, formulaName);
            //删除字符串开头结尾的括号 --（）
            String s1 = s.substring(1, s.length() - 1);
            fieldsList = Arrays.asList(s1.split(","));
            return fieldsList;
        }
        return fieldsList;
    }

    /**
     * 替换count公式中负责人字段
     *
     * @param s 计数、去重计数公式
     * @param formIdTableMap null为数据集、否则为ck
     * @return java.lang.String
     * @author zyy
     * @date 2023/3/2
     * @version v1.0
     */
    private String replaceOwnerId(String s,Map<Long, String> formIdTableMap) {
        String replaceKeywords = "";
        if(s.contains(FieldTypeEnum.OWNERID.getAlias())){
            if(Objects.isNull(formIdTableMap)){
                //数据集调用 ownerId_x -> myTeamMain_x
                replaceKeywords = XbbConstant.MY_TEAM_MAIN.replace(XbbConstant.SEPARATOR,"");
            }else{
                replaceKeywords = XbbConstant.TEAMMAIN;
            }
            s = s.replaceAll(FieldTypeEnum.OWNERID.getAlias(),replaceKeywords);
        }
        return s;
    }

    /**
     * 获取除数
     *
     * @param formIdTableMap
     * @param divisorStr
     * @param source
     * @author zyy
     * @date 2022/8/24
     * @version v1.0
     */
    private String getDivisorStr(Map<Long, String> formIdTableMap, String divisorStr, String source) {
        StringBuilder sb = new StringBuilder(divisorStr);
        if(!source.contains(XbbConstant.JSON_EXTRACT_DOUBLE)){
            translateBaseSql(formIdTableMap, sb, source);
        }else{
            sb.append(source);
        }
        return sb.toString();
    }

    /**
     * 解析计算字段的formula，只解析最外层 （嵌套的情况会进行递归调用）
     *
     * @param formula
     * @return java.util.List<java.lang.String>
     * @author zyy
     * @date 2022/7/25
     * @version v1.0
     */
    public List<String> analysisFormula(List<String> fieldsList,String formula) {
        String fields = RegexUtil.formulaFieldPattern.split(formula)[1];
        //建立栈，控制括号的匹配
        Stack stack = new Stack();
        Matcher matcher = RegexUtil.formulaFieldsPattern.matcher(fields);
        //拼接（公式作为变量）
        StringBuilder sb = new StringBuilder();
        //变量集合
        while (matcher.find()) {
            String group = matcher.group();
            if (group.startsWith("[") && group.contains("]")) {
                fieldsList.add(group);
            } else if (group.contains("(") && !group.contains(")")) {
                sb.append(group);
                stack.push(1);
            } else {
                //如果栈空
                if (stack.empty()) {
                    if (",".equals(group)){
                        continue;
                    }else if (RegexUtil.isNumber(group) && RegexUtil.checkNegative.matcher(group).matches()){
                        fieldsList.add("(" + group + ")");
                    }else {
                        fieldsList.add(group);
                    }
                } else {//如果栈不为空
                    sb.append(group);
                    if (")".equals(group)) {
                        stack.pop();
                        if (stack.empty()) {
                            fieldsList.add(sb.toString());
                            sb.delete(0, sb.length());
                        }
                    }
                }
            }
        }
        return fieldsList;
    }


    private  void translateBaseSql(Map<Long, String> formIdTableMap, StringBuilder textFormulaString, String s) {
        // s ->  {表单id.字段名}
        if(isContainPoint(s)){
            //countFlag为ture时在上一层手动在结尾添加了COUNT
            boolean countFlag = s.endsWith(XbbConstant.COUNT);
            String formIdAndAttr = s.replaceAll("\\{", "").replaceAll("\\}", "").replaceAll(XbbConstant.COUNT,"");
            String[] formIdAndAttrArray = formIdAndAttr.split("\\"+XbbConstant.POINT);

            String column = "";
            if (formIdAndAttrArray.length>1){
                column = formIdAndAttrArray[1];
            }

            String tableName = "";
            if(Objects.nonNull(formIdTableMap)){
                tableName = formIdTableMap.get(Long.valueOf(formIdAndAttrArray[0]));
            }else{
                //数据集调用时formIdTableMap = null, 不需要组装JSON类的提取函数， 直接返回字段名即可
                if(!countFlag){
                    column = XbbConstant.IF_NULL + XbbConstant.L_P + column + XbbConstant.SQL_COMMA + XbbConstant.ZERO + XbbConstant.R_P;
                }
                textFormulaString.append(column);
                return;
            }

            column = getRealCol(tableName, column);

            //data 内外的区分
            if(column.contains(XbbConstant.SEPARATOR)){
                if(countFlag){
                    textFormulaString.append(XbbConstant.L_P)
                            .append(XbbConstant.JSON_EXTRACT).append(XbbConstant.L_P).append(tableName).append(XbbConstant.POINT).append(XbbConstant.DATA).append(",")
                            .append("'").append(column).append("'").append(",").append("'").append("Nullable(String)").append("'").append(XbbConstant.R_P).append(XbbConstant.R_P);
                }else{
                    textFormulaString.append(XbbConstant.L_P)
                            .append(XbbConstant.JSON_EXTRACT_DOUBLE).append(XbbConstant.L_P).append(tableName).append(XbbConstant.POINT).append(XbbConstant.DATA).append(",")
                            .append("'").append(column).append("'").append(XbbConstant.R_P).append(XbbConstant.R_P);
                }
            }else {
                String dataBaseString = StringUtil.getDataBaseString(column);
                if (StringUtils.contains(dataBaseString, XbbConstant.UPDATE_TIME) ||
                        StringUtils.contains(dataBaseString,XbbConstant.ADD_TIME )){
                    textFormulaString.append(XbbConstant.L_P).append(tableName).append(XbbConstant.POINT)
                            .append(dataBaseString).append(XbbConstant.R_P);
                }else if(countFlag){
                    if(column.contains(XbbConstant.TEAMMAIN)){
                        //count负责人字段时需要使用团队表
                        if(XbbTableRefEnum.PAAS.getTableName().equals(tableName)){
                            tableName = XbbTableRefEnum.PAAS_TEAM.getTableName();
                        }else if(XbbTableRefEnum.WORK_ORDER.getTableName().equals(tableName)){
                            tableName = XbbTableRefEnum.WORK_ORDER_TEAM.getTableName();
                        }else{
                            //业务默认团队表名称都是加上 _user 的后缀
                            tableName = tableName +XbbConstant.SEPARATOR + BasicConstant.USER;
                        }
                    }
                    textFormulaString.append(XbbConstant.L_P).append(tableName).append(XbbConstant.POINT)
                            .append(dataBaseString).append(XbbConstant.R_P);
                }else {
                    textFormulaString.append(XbbConstant.L_P).append(XbbConstant.TO_FLOAT_64).append(XbbConstant.L_P).append(tableName).append(XbbConstant.POINT)
                            .append(dataBaseString).append(XbbConstant.R_P).append(XbbConstant.R_P);
                }

            }
        }else {
            textFormulaString.append(s);
        }
    }

    private boolean isContainPoint(String s) {
        return s.contains(XbbConstant.POINT) && !s.contains(XbbTableRefEnum.CONTRACT_PERFORMANCE.getTableName())
                && !s.contains(XbbTableRefEnum.PURCHASE_PRODUCT.getTableName()) && !s.contains(XbbTableRefEnum.TRANSFER_PRODUCT.getTableName())
                && !s.contains(XbbTableRefEnum.PRODUCTION_ORDER_PRODUCT.getTableName()) && !s.contains(XbbTableRefEnum.RETURNEDPURCHASE_PRODUCT.getTableName())
                && !RegexUtil.isNumber(s);
    }

    private String getRealCol(String tableName, String column) {
        if (Objects.equals(tableName, XbbTableRefEnum.CONTRACT_PERFORMANCE.getTableName())) {
            column = ContractPerformanceEnum.getRealAttr(column);
        }else if (Objects.equals(tableName, XbbTableRefEnum.PURCHASE_PRODUCT.getTableName())) {
            if (Objects.equals(column, SelectProductEnum.INSTOCK_NUM.getAttr())){
                column = RelatedProductConstant.INSTOCK_NUM;
            }else if (Objects.equals(column, SelectProductEnum.RETURN_NUM.getAttr())){
                column = RelatedProductConstant.OUTSTOCK_NUM;
            }
        }else if (Objects.equals(tableName, XbbTableRefEnum.TRANSFER_PRODUCT.getTableName())) {
            if (Objects.equals(column, SelectProductEnum.INSTOCK_NUM.getAttr())){
                column = RelatedProductConstant.INSTOCK_NUM;
            }
        }else if (Objects.equals(tableName, XbbTableRefEnum.PRODUCTION_ORDER_PRODUCT.getTableName())
                || Objects.equals(tableName, XbbTableRefEnum.RETURNEDPURCHASE_PRODUCT.getTableName())) {
            if (Objects.equals(column, SelectProductEnum.OUTSTOCK_NUM.getAttr())){
                column = RelatedProductConstant.OUTSTOCK_NUM;
            }
        }
        return column;
    }

    private  String translateBaseSql(Map<Long, String> formIdTableMap,  String s) {
        if(s.contains(XbbConstant.POINT)){
            StringBuilder textFormulaString = new StringBuilder();
            translateBaseSql(formIdTableMap, textFormulaString,s);
            return textFormulaString.toString();
        }
        return s;
    }

    /**
     * 根据计算类型拼接sql
     * eg： sum(xxx)
     *
     * @param aggTypeEnum  计算类型
     * @param formulaStr   计算字段的formula
     * @param isBracket    是否需要括号
     * @return java.lang.String
     * @author zyy
     * @date 2022/7/25
     * @version v1.0
     */
    public String getFormulaByAggType(AggTypeEnum aggTypeEnum, String formulaStr, boolean isBracket) {
        //agg已经拼接过sum了，这里直接返回
        if(Objects.equals(aggTypeEnum,AggTypeEnum.AGG)){
            return formulaStr;
        }
        if(aggTypeEnum == null && StringUtil.isNotEmpty(formulaStr)){
            return  isBracket ? "(" + formulaStr + ")" : formulaStr;
        }
        return isBracket ?
                aggTypeEnum.getAlias().toUpperCase() + "(" + formulaStr + ")" :
                aggTypeEnum.getAlias().toUpperCase() + formulaStr;
    }


    /**
     * 处理计算字段
     *
     * @author zyy
     * @date 2022/10/24
     * @version v1.0
     */
    public void addComputedField(ChartEntity chartEntity, FieldAttrPojo fieldAttrPojo, AggTypeEnum aggTypeEnum, String side, HashMap<String, Set<String>> selectItemMap,Map<Long, String> formIdTableMap, Map<String, AggFieldPojo> aggFieldMap, List<String> aggFieldsList, Map<String, String> sortMap, List<String> sumTotalFieldsList,List<String> subQuerySqlOfCountList,List<String> summaryColumnAlisa) throws XbbException {
        if(specialCaseFilter(chartEntity,fieldAttrPojo,aggTypeEnum)){
            buildComputedField(chartEntity,side,formIdTableMap,aggFieldMap,aggFieldsList,fieldAttrPojo,aggTypeEnum,sortMap,sumTotalFieldsList,selectItemMap,subQuerySqlOfCountList,summaryColumnAlisa);
        }else{
            addAggComputedField(chartEntity,selectItemMap,side, formIdTableMap, aggFieldMap, aggFieldsList, fieldAttrPojo, aggTypeEnum, fieldAttrPojo.getAttr(), sortMap, sumTotalFieldsList,subQuerySqlOfCountList,summaryColumnAlisa);
        }
    }

    /**
     * 统计表获取计算字段的特殊过滤
     *
     * @param chartEntity
     * @param fieldAttrPojo
     * @param aggType
     * @return boolean
     * @author zyy
     * @date 2022/8/2
     * @version v1.0
     */
    private boolean specialCaseFilter(ChartEntity chartEntity, FieldAttrPojo fieldAttrPojo, AggTypeEnum aggType) {
        //非统计表、指标图返回false
        if(!ChartTypeEnum.supportAggType(chartEntity.getChartType())){
            return false;
        }
        //汇总类型为sum返回false
        if(Objects.equals(aggType,AggTypeEnum.SUM)){
            return false;
        }
        JSONObject formulaInfo = fieldAttrPojo.getFormulaInfo();
        String formula = formulaInfo.getString("formula");
        //最外层公式的大写英文
        String formulaName = RegexUtil.extractFormulaName(formula);
        //是否走聚合逻辑
        return !AggTypeEnum.notSupportAgg(aggType,formulaName);
    }


    /**
     * 构建统计表计算字段的sql语句
     *
     * @param chartEntity 图表实体
     * @param side  轴方位
     * @param formIdTableMap formId 映射容器
     * @param aggFieldMap 汇总字段映射容器
     * @param aggFieldsList 汇总字段数组
     * @param fieldAttrPojo 当前汇总字段实体
     * @param aggType 汇总类型
     * @param sortMap 用户设置的排序字段容器
     * @param sumTotalFieldsList 汇总字段的sql数组
     * @author zyy
     * @date 2022/10/8
     * @version v1.0
     */
    private void buildComputedField(ChartEntity chartEntity, String side, Map<Long, String> formIdTableMap, Map<String, AggFieldPojo> aggFieldMap, List<String> aggFieldsList, FieldAttrPojo fieldAttrPojo, AggTypeEnum aggType, Map<String, String> sortMap, List<String> sumTotalFieldsList,HashMap<String, Set<String>> selectItemMap,List<String> subQuerySqlOfCountList,List<String> summaryColumnAlisa) throws XbbException {
        JSONObject formulaInfo = fieldAttrPojo.getFormulaInfo();
        Boolean countFlag = formulaInfo.getBoolean("countFlag");
        //countFlag
        if(Objects.nonNull(countFlag) && countFlag){
            //只有计算公式中存在count、countd函数时才会走这段逻辑
            buildComputeSqlOfCount(chartEntity,formulaInfo,fieldAttrPojo,formIdTableMap,aggFieldsList,aggType,sumTotalFieldsList,selectItemMap,subQuerySqlOfCountList);
        }else{
            //完整的公式
            String formula = formulaInfo.getString("formula");
            //最外层公式的大写英文
            String formulaName = RegexUtil.extractFormulaName(formula);
            List<String> splitResultList = new ArrayList<>();
            //默认进行计算公式拆分，false则不拆分直接解析整个公式
            boolean splitFormulaFlag = splitFormula(splitResultList,formula,formulaName,aggType);
            //重建sql
            StringBuilder newComp = new StringBuilder();
            for (int j = 0; j < splitResultList.size(); j++) {
                String formulaByAggType = splitResultList.get(j);
                if(splitFormulaFlag){
                    //拆解后需要对每部分公式手动拼上汇总方式
                    formulaByAggType = getFormulaByAggType(AggTypeEnum.SUM,splitResultList.get(j),Boolean.TRUE);
                }
                //获取sql
                String formulaSql = getString(formulaInfo,selectItemMap,formulaByAggType,formIdTableMap);
                //sql格式后置处理
                if(AggTypeEnum.isAggType(aggType)){
                    //聚合方式的sql格式为sum(字段A)/sum(字段B)， 因此每获取一次部分sql，都先嵌套一层sum，再去对每层sql拼接计算符号，获取总的sql。
                    formulaSql = getFormulaByAggType(AggTypeEnum.SUM,formulaSql,Boolean.FALSE);
                }else{
                    // 计算公式非逻辑运算，并且汇总方式为非聚合，这里需要摘掉前半部分最后的括号跟后半部分的第一个括号
                    if(splitFormulaFlag){
                        StringBuilder sb = new StringBuilder(formulaSql);
                        if(j == splitResultList.size() - 1){
                            sb.replace(0,1,"");
                        }else{
                            sb.replace(formulaSql.length() - 1,formulaSql.length(),"");
                        }
                        formulaSql = sb.toString();
                    }
                }
                newComp.append(formulaSql);
                if (j < splitResultList.size() - 1) {
                    newComp.append(" ");
                    newComp.append(BiMathEnum.getArithmetic4Formula(formulaName));
                    newComp.append(" ");
                }
            }
            //计算公式为平均值时的特殊场景
            if(BiMathEnum.AVERAGE.getFormula().equals(formulaName) && AggTypeEnum.isAggType(aggType)){
                //先对分子套一层括号
                newComp = new StringBuilder(getFormulaByAggType(null,newComp.toString(),true));
                //接着组装分子 / 分母，并套最外层括号
                newComp.insert(0,XbbConstant.L_P);
                newComp.append(" ").append(BiMathEnum.DIVIDE.getArithmetic()).append(" ").append(splitResultList.size());
                newComp.append(XbbConstant.R_P);
            }
            String resultStr = newComp.toString();
            Boolean needBracket = BiMathEnum.DATE_DIFF.getFormula().equals(formulaName) ? Boolean.TRUE : Boolean.FALSE;
            resultStr = getFormulaByAggType(aggType,resultStr,needBracket) + " AS " + fieldAttrPojo.getAttr();
            aggFieldsList.add(resultStr);
            JSONArray advancedComputing = fieldAttrPojo.getAdvancedComputing();
            if (AdvancedComputingFirstEnum.noneYearChain(advancedComputing)) {
                sumTotalFieldsList.add(resultStr);
            }
            summaryColumnAlisa.add(fieldAttrPojo.getAttr());
        }
        //设置aggFieldMap数据，供下游图表图表渲染处使用
        fieldInduction(aggType,side,fieldAttrPojo.getAttr(),aggFieldMap,fieldAttrPojo,sortMap);
    }

    private void buildComputeSqlOfCount(ChartEntity chartEntity,JSONObject formulaInfo, FieldAttrPojo fieldAttrPojo, Map<Long, String> formIdTableMap, List<String> aggFieldsList, AggTypeEnum aggType, List<String> sumTotalFieldsList, HashMap<String, Set<String>> selectItemMap,List<String> subQuerySqlOfCountList) throws XbbException {
        //完整的公式
        String formula = formulaInfo.getString("formula");
        //第一层函数的大写英文
        String firstFormulaName = RegexUtil.extractFormulaName(formula);
        String asAttrPre = fieldAttrPojo.getAttr();

        //拆解计算公式
        List<String> fieldsList = new ArrayList<>();
        if(countFlag(firstFormulaName)){
            //单独设置COUNT或COUNTD时直接加入数组中
            fieldsList.add(formula);
        }else{
            analysisFormula(fieldsList, formula);
        }

        // count、countd公式 ->  对应的SQL
        Map<String,String> sql4FormulaByCountMap = new LinkedHashMap<>(PaasConstant.DEFAULT_INITIAL_CAPACITY);
        // 非count、countd公式 -> 对应的SQL
        Map<String,String> sql4FormulaMap = new LinkedHashMap<>(PaasConstant.DEFAULT_INITIAL_CAPACITY);
        // 计算公式对应的内层sql别名
        Map<String,String> innerSqlAlisaMap = new LinkedHashMap<>(PaasConstant.DEFAULT_INITIAL_CAPACITY);

        //------------解析每部分公式----------------
        for (int i = 0; i < fieldsList.size(); i++) {
            String formulaStr = fieldsList.get(i);
            //获取各部分公式的sql
            String sql = formulaTranslate(formulaStr,new ArrayList<>(),formIdTableMap);
            if (countFlag(formulaStr)){
                //count部分sql别名
                String countSqlAlisa = asAttrPre + "_count_" + i;
                //count sql主体
                String countSql = dealCountSql(chartEntity,formulaStr,countSqlAlisa,sql,formIdTableMap);
                //保存数据
                sql4FormulaByCountMap.put(formulaStr,countSql);
                innerSqlAlisaMap.put(formulaStr,countSqlAlisa);
                //处理selectItemMap
                addSelectCol(formIdTableMap,selectItemMap,fieldAttrPojo,formulaStr);
            }else{
                //非count部分有特殊逻辑，在各个汇总方式逻辑中单独处理
                if(sql4FormulaMap.containsKey(formulaStr)){
                    //公式拆分后存在一模一样的配置时，也存到容器中
                    formulaStr = formulaStr + XbbConstant.SEPARATOR + i;
                }
                sql4FormulaMap.put(formulaStr,sql);
            }
        }
        //保存内层count函数sql
        aggFieldsList.addAll(sql4FormulaByCountMap.values());
        if(AdvancedComputingFirstEnum.noneYearChain(fieldAttrPojo.getAdvancedComputing())){
            sumTotalFieldsList.addAll(sql4FormulaByCountMap.values());
        }
        //插入all_count语句
        insertAlCountSql(aggFieldsList,fieldAttrPojo,sumTotalFieldsList,chartEntity.getChartType());

        //组装通用参数
        SubSqlParamPojo subSqlParamPojo = SubSqlParamPojo.init(chartEntity,fieldsList,fieldAttrPojo,aggFieldsList,
                sumTotalFieldsList,subQuerySqlOfCountList,sql4FormulaByCountMap,
                sql4FormulaMap,innerSqlAlisaMap,firstFormulaName,aggType);
        //--------------根据汇总方式进行拆分-----------
        switch (aggType){
            case MAX:
            case MIN:
                dealSubSqlOfJudge(subSqlParamPojo);
                break;
            case AVG:
                dealSubSqlOfAvg(subSqlParamPojo);
                break;
            case AGG:
                dealSubSqlOfAgg(subSqlParamPojo);
                break;
            case SUM:
                dealSubSqlOfDefault(subSqlParamPojo,Boolean.TRUE);
                break;
            default:
                throw new XbbException(BiErrorCodeEnum.API_ERROR_241054);
        }
    }

    /**
     * 计数公式中设置的字段可能不在data内，因此count公式需要反向影响selectItemMap
     *
     * @param formIdTableMap
     * @param selectItemMap
     * @param fieldAttrPojo
     * @author zyy
     * @date 2023/4/3
     * @version v1.0
     */
    private void addSelectCol(Map<Long, String> formIdTableMap, HashMap<String, Set<String>> selectItemMap, FieldAttrPojo fieldAttrPojo,String formulaStr) {
        if (Objects.isNull(formIdTableMap) || Objects.isNull(selectItemMap)){
            //数据集图表的字段全部拉平，不分层;  只对ck图表进行字段处理；
            return;
        }
        //COUNT({formId.serialNo}) 或者 COUNT({formId.creatorId})
        if(!formulaStr.contains(BasicConstant.SERIAL_NO) && !formulaStr.contains(StringConstant.CREATOR_ID)){
            return;
        }
        String tmpItemName = "";
        if(formulaStr.contains(BasicConstant.SERIAL_NO)){
            tmpItemName = BasicConstant.SERIAL_NO;
        }else if(formulaStr.contains(StringConstant.CREATOR_ID)){
            tmpItemName = StringConstant.CREATOR_ID;
        }

        String itemStr = initSqlHelper.generatargMax(StringUtil.getDataBaseString(tmpItemName));
        Long formId = fieldAttrPojo.getFormId();
        initSqlHelper.addSelectCol(formIdTableMap,selectItemMap,formId,itemStr);
    }

    private String dealCountSql(ChartEntity chartEntity, String formulaStr, String countSqlAlisa,String sql,Map<Long, String> formIdTableMap) {
        if(!Objects.equals(chartEntity.getChartType(),ChartTypeEnum.DATA_QUERY_TABLE.getCode())){
            //非查询表直接拼接count前缀
            return AggTypeEnum.assembleSqlByType(AggTypeEnum.COUNT.getAlias(), sql, countSqlAlisa);
        }

        //---------------------以下是查询表特殊处理---------------------
        //不能组装聚合函数前缀, 每行count 恒为0或1
        String condValue = XbbConstant.SINGLE_QUOTES + XbbConstant.SINGLE_QUOTES;
        String symbolValue = XbbConstant.NOT_EQUAL;
        if(formulaStr.contains(TabConstant.OWNERID)){
            condValue = XbbConstant.SQL_NULL;
            //数据集的符号的方言跟ck不同，需要做区分
            symbolValue = XbbConstant.IS_NOT;
        }
        //查询表使用去重计数时需要将DISTINCT置空，否则格式有误
        if(sql.contains(XbbConstant.DISTINCT)){
            sql =  sql.replaceAll(XbbConstant.DISTINCT, "");
        }

        String ifFunction = InitSqlHelper.ifFunction(sql, symbolValue,condValue , BasicConstant.ONE_STRING, BasicConstant.ZERO_STRING);
        sql = ifFunction + XbbConstant.AS + countSqlAlisa;
        return sql;
    }

    /**
     * 子查询SQL处理（最大、最小值）
     *
     * @param subSqlParamPojo 入参实体
     * @author zyy
     * @date 2023/2/27
     * @version v1.0
     */
    public void dealSubSqlOfJudge(SubSqlParamPojo subSqlParamPojo) {
        dealSubSqlOfDefault(subSqlParamPojo,Boolean.TRUE);
    }

    /**
     * 子查询SQL处理（平均）
     *
     * @param subSqlParamPojo
     * @author zyy
     * @date 2023/2/27
     * @version v1.0
     */
    private void dealSubSqlOfAvg(SubSqlParamPojo subSqlParamPojo) {
        //复用求和逻辑，获取主体sql
        String sql = dealSubSqlOfDefault(subSqlParamPojo,Boolean.FALSE);
        FieldAttrPojo fieldAttrPojo = subSqlParamPojo.getFieldAttrPojo();
        String asAttrPre = fieldAttrPojo.getAttr();

        //组装平均语句的SQL
        StringBuilder avgSqlBuilder = new StringBuilder();
        avgSqlBuilder.append(XbbConstant.L_P).append(sql).append(XbbConstant.R_P)
                .append(BiMathEnum.DIVIDE.getArithmetic()).append(BiConstant.COUNT_ALISA)
                .append(XbbConstant.AS).append(asAttrPre);

        //保存子查询外层sql
        subSqlParamPojo.getSubQuerySqlOfCountList().add(avgSqlBuilder.toString());
    }

    /**
     * 子查询SQL处理（聚合）
     *
     * @param subSqlParamPojo 入参实体
     * @author zyy
     * @date 2023/2/21
     * @version v1.0
     */
    public void dealSubSqlOfAgg(SubSqlParamPojo subSqlParamPojo) {
        //聚合汇总方式，count部分都不使用all_count

        FieldAttrPojo fieldAttrPojo = subSqlParamPojo.getFieldAttrPojo();
        String prefixAlisa = fieldAttrPojo.getAttr();
        List<String> subQuerySqlOfCountList = subSqlParamPojo.getSubQuerySqlOfCountList();
        Map<String, String> innerSqlAlisaMap = subSqlParamPojo.getInnerSqlAlisaMap();
        Map<String, String> sql4FormulaMap = subSqlParamPojo.getSql4FormulaMap();
        //获取计算符号
        BiMathEnum biMathEnum = BiMathEnum.getBiMathEnum4Formula(subSqlParamPojo.getFirstFormulaName());
        String arithmetic4Formula = BiMathEnum.getArithmetic4Formula(subSqlParamPojo.getFirstFormulaName());
        //循环每个非count部分，同时拼接sum前缀
        int index = 0;
        for (String formulaStr : sql4FormulaMap.keySet()) {
            //设置SQL别名
            String sumAlisa = prefixAlisa + "_attr_" + index++;
            //拼接SUM函数前缀
            String formulaSql = sql4FormulaMap.get(formulaStr);
            String sumSql = AggTypeEnum.assembleSqlByType(AggTypeEnum.SUM.getAlias(), formulaSql, sumAlisa);
            //保存SQL与别名到对应容器中
            innerSqlAlisaMap.put(formulaStr,sumAlisa);
            //保存非count部分的字段sql到对应容器中
            saveAggFieldSql(fieldAttrPojo,sumSql,subSqlParamPojo.getAggFieldsList(),subSqlParamPojo.getSumTotalFieldsList());
        }

        //处理外层字段SQL
        List<String> fieldsList = subSqlParamPojo.getFieldsList();
        StringBuilder outSqlBuilder = new StringBuilder();
        for (int i = 0; i < fieldsList.size(); i++) {
            String formulaStr = fieldsList.get(i);
            String innerAlisa = innerSqlAlisaMap.get(formulaStr);
            //拼接符号
            if(i != 0){
                outSqlBuilder.append(arithmetic4Formula);
            }
            outSqlBuilder.append(XbbConstant.L_P).append(innerAlisa).append(XbbConstant.R_P);
        }

        //特殊格式处理
        String outSql = specialSqlByAgg(outSqlBuilder,biMathEnum,fieldsList.size());
        //保存外层sql
        subQuerySqlOfCountList.add(outSql + XbbConstant.AS + prefixAlisa);

    }

    /**
     * 求和外层sql的特殊格式处理
     *
     * @param outSqlBuilder
     * @param biMathEnum
     * @param size
     * @return java.lang.String
     * @author zyy
     * @date 2023/2/21
     * @version v1.0
     */
    private String specialSqlByAgg(StringBuilder outSqlBuilder, BiMathEnum biMathEnum, int size) {
        String outSql = outSqlBuilder.toString();
        if(Objects.equals(biMathEnum,BiMathEnum.AVERAGE)){
            outSql = outSqlBuilder.insert(0,XbbConstant.L_P).append(XbbConstant.R_P).append(BiMathEnum.DIVIDE.getArithmetic()).append(size).toString();
        }
        return outSql;
    }

    /**
     * 子查询SQL处理（求和）
     *
     * @param subSqlParamPojo 入参实体
     * @param needDealOutSql 外层sql是否需要拼接别名
     * @author zyy
     * @date 2023/2/20
     * @version v1.0
     */
    public String dealSubSqlOfDefault(SubSqlParamPojo subSqlParamPojo,boolean needDealOutSql) {
        //agg、max、min、sum都会走该方法。总体逻辑相同，个别汇总类型的处理方式有区别。

        //获取计算符号
        BiMathEnum biMathEnum = BiMathEnum.getBiMathEnum4Formula(subSqlParamPojo.getFirstFormulaName());
        String arithmetic4Formula = BiMathEnum.getArithmetic4Formula(subSqlParamPojo.getFirstFormulaName());
        //公式内是否只包含count、countd
        boolean onlyCount = subSqlParamPojo.getSql4FormulaMap().size() == 0 ? Boolean.TRUE : Boolean.FALSE;
        //判断是否为max、min汇总方式
        AggTypeEnum aggType = subSqlParamPojo.getAggType();
        boolean judgeFlag = Objects.equals(aggType,AggTypeEnum.MAX) || Objects.equals(aggType,AggTypeEnum.MIN);

        //解析非count部分: 多个非count字段结合到一个聚合函数内
        FieldAttrPojo fieldAttrPojo = subSqlParamPojo.getFieldAttrPojo();
        String prefixAlisa = fieldAttrPojo.getAttr();
        if(!onlyCount){
            //一个计算字段内拆解后最多有个一个sum，prefixAlisa用来区分不同的计算字段
            String sumAlisa = prefixAlisa + "_attr_1";

            //替换计算符号
            String replaceArithmetic = replaceArithmetic4Formula(biMathEnum,subSqlParamPojo.getFieldsList());

            //生成sql主体语句
            List<String> alisaList = new ArrayList<>(subSqlParamPojo.getSql4FormulaMap().values());
            String tmpSql = String.join(replaceArithmetic, alisaList);
            if(Objects.equals(BiMathEnum.DIVIDE,biMathEnum) && !BiMathEnum.DIVIDE.getArithmetic().equals(replaceArithmetic)){
                //除法，并且第一个参数为count函数，这里需要转成分数的形式
                //count / 9 + count / 4 + count / 7 = count * (1 / 3 + 1 /4 + 1 / 7) = count * SUM(1 / N1)
                tmpSql = BasicConstant.ONE_STRING + BiMathEnum.DIVIDE.getArithmetic() + XbbConstant.L_P + tmpSql + XbbConstant.R_P;
                arithmetic4Formula = BiMathEnum.MULTIPLY.getArithmetic();
            }
            String contentSql = XbbConstant.L_P + tmpSql + XbbConstant.R_P;

            //sql语句拼接聚合函数,得到完整sql语句
            Integer chartType = subSqlParamPojo.getChartEntity().getChartType();
            String sumSql = spliceOuterSqlAggFunctionName(chartType,aggType,contentSql,sumAlisa,biMathEnum,replaceArithmetic);

            //保存数据
            subSqlParamPojo.getInnerSqlAlisaMap().put("sumAlisa",sumAlisa);
            saveAggFieldSql(fieldAttrPojo,sumSql,subSqlParamPojo.getAggFieldsList(),subSqlParamPojo.getSumTotalFieldsList());
        }

        //获取外层sql
        StringBuilder outSqlBuilder = new StringBuilder();
        //标记非count部分的外层sql逻辑是否已经处理过了
        boolean appendSumSql = false;
        List<String> fieldsList = subSqlParamPojo.getFieldsList();
        for (int i = 0; i < fieldsList.size(); i++) {
            //获取外层公式对应的内层sql别名
            String formulaStr = fieldsList.get(i);
            String alisaKey =  subSqlParamPojo.getSql4FormulaByCountMap().containsKey(formulaStr)  ? formulaStr : "sumAlisa";
            String innerAlisa = subSqlParamPojo.getInnerSqlAlisaMap().get(alisaKey);

            //非count部分只处理一次（）
            boolean countFlag = StringUtils.isNotBlank(alisaKey) && !"sumAlisa".equals(alisaKey);
            if(!countFlag && appendSumSql){
                continue;
            }

            //拼接计算符号
            if(i != 0){
                outSqlBuilder.append(arithmetic4Formula);
            }

            //处理外层拼接all_count语句
            if(countFlag){
                outSqlBuilder.append(XbbConstant.L_P).append(innerAlisa);
                // max、min汇总方式， 乘法、除法计算公式时 不需要 拼all_count
                if(!judgeFlag && !BiMathEnum.MULTIPLY.equals(biMathEnum) && !BiMathEnum.DIVIDE.equals(biMathEnum)){
                    outSqlBuilder.append(BiMathEnum.MULTIPLY.getArithmetic()).append(BiConstant.COUNT_ALISA);
                }
                outSqlBuilder.append(XbbConstant.R_P);
            }else {
                appendSumSql = true;
                outSqlBuilder.append(XbbConstant.L_P).append(innerAlisa).append(XbbConstant.R_P);
            }
        }

        //特殊格式处理
        String outSql = specialSqlByDefault(outSqlBuilder,biMathEnum,onlyCount,fieldsList.size(),judgeFlag);
        //保存外层sql : avg时也会调用该方法并在调用层进行别名拼接
        if(needDealOutSql){
            outSql = outSql + XbbConstant.AS + prefixAlisa;
            subSqlParamPojo.getSubQuerySqlOfCountList().add(outSql);
        }
        return outSql;
    }

    /**
     * 拼接聚合函数前缀
     *
     * @param chartType 图表类型
     * @param aggType 汇总方式
     * @param contentSql sql主体内容
     * @param sumAlisa sql别名
     * @param biMathEnum 计算公式枚举
     * @param replaceArithmetic 替换后的计算公式符号
     * @return java.lang.String
     * @author zyy
     * @date 2023/2/28
     * @version v1.0
     */
    private String spliceOuterSqlAggFunctionName(Integer chartType, AggTypeEnum aggType, String contentSql, String sumAlisa,BiMathEnum biMathEnum,String replaceArithmetic) {
        if(Objects.equals(AggTypeEnum.AVG,aggType)){
            //平均方式时默认给SUM
            aggType = AggTypeEnum.SUM;
        }
        String aggAlisa = aggType.getAlias();

        //max、min时是否已经替换了计算符号
        boolean replaceFlag = biMathEnum != null && !biMathEnum.getArithmetic().equals(replaceArithmetic);
        if((Objects.equals(AggTypeEnum.MAX, aggType) || Objects.equals(AggTypeEnum.MIN,aggType)) && replaceFlag){
            if (Objects.equals(BiMathEnum.SUBTRACT,biMathEnum)){
                //max、min时反转函数的前缀
                aggAlisa = AggTypeEnum.reverseMaxOrMin(aggType);
            }
        }

        //查询表时特殊处理
        if(Objects.equals(chartType,ChartTypeEnum.DATA_QUERY_TABLE.getCode())){
            if(Objects.equals(AggTypeEnum.MAX, aggType) || Objects.equals(AggTypeEnum.MIN,aggType)){
                //max、min时查询表使用的函数需要转成行维度
                aggAlisa = AggTypeEnum.getRowAlias4MaxOrMin(aggAlisa);
            }else{
                //sum、avg时不拼接聚合函数前缀
                aggAlisa = "";
            }
        }
        return AggTypeEnum.assembleSqlByType(aggAlisa, contentSql, sumAlisa);
    }

    /**
     * 求和-减法、除法时替换计算符号
     *
     * @param biMathEnum
     * @param fieldsList
     * @return java.lang.String
     * @author zyy
     * @date 2023/2/27
     * @version v1.0
     */
    private String replaceArithmetic4Formula(BiMathEnum biMathEnum, List<String> fieldsList) {
        String arithmetic = biMathEnum.getArithmetic();
        String firstFormula = fieldsList.get(0);
        //第一个公式不是count、countd就不用转换符号：是因为非count部分会聚合到一个sum中，只有sum部分作为减数或者除数才需要更换sum内的计算符号
        if(!firstFormula.contains(XbbConstant.COUNT) && !firstFormula.contains(XbbConstant.COUNTD)){
            return arithmetic;
        }
        if (Objects.equals(BiMathEnum.SUBTRACT,biMathEnum)){
            //减号替换为加号
            arithmetic = BiMathEnum.ADD.getArithmetic();
        }
        if(Objects.equals(BiMathEnum.DIVIDE,biMathEnum)){
            //除号替换为乘号
            arithmetic = BiMathEnum.MULTIPLY.getArithmetic();
        }
        return arithmetic;
    }

    /**
     * 保存字段SQL到汇总字段数组中
     *
     * @param fieldAttrPojo 字段实体
     * @param sumSql 非count部分计算公式的sql
     * @param aggFieldsList 汇总字段sql数组
     * @param sumTotalFieldsList 汇总字段sql数组（统计表使用）
     * @author zyy
     * @date 2023/2/21
     * @version v1.0
     */
    private void saveAggFieldSql(FieldAttrPojo fieldAttrPojo, String sumSql, List<String> aggFieldsList, List<String> sumTotalFieldsList) {
        aggFieldsList.add(sumSql);
        if(AdvancedComputingFirstEnum.noneYearChain(fieldAttrPojo.getAdvancedComputing())){
            sumTotalFieldsList.add(sumSql);
        }
    }

    /**
     * 求和外层sql的特殊格式处理
     *
     * @param outSqlBuilder 外层sql
     * @param biMathEnum 计算公式第一层函数枚举
     * @param onlyCount 计算公式是否只存在计数、去重计数函数
     * @param size 计算公拆解后的数量
     * @param judgeFlag true-max、min方式调用   false-avg、sum调用
     * @return java.lang.String
     * @author zyy
     * @date 2023/2/21
     * @version v1.0
     */
    private String specialSqlByDefault(StringBuilder outSqlBuilder, BiMathEnum biMathEnum, boolean onlyCount, int size, boolean judgeFlag) {
        String outSql = outSqlBuilder.toString();
        //平均公式特殊格式处理
        if(BiMathEnum.AVERAGE.equals(biMathEnum)){
            outSql = outSqlBuilder.insert(0,XbbConstant.L_P).append(XbbConstant.R_P).append(BiMathEnum.DIVIDE.getArithmetic()).append(size).toString();
        }
        //乘法、除法特殊格式处理
        else if (BiMathEnum.MULTIPLY.equals(biMathEnum) || BiMathEnum.DIVIDE.equals(biMathEnum)){
            if (onlyCount && !judgeFlag){
                outSql = outSqlBuilder.insert(0,XbbConstant.L_P).append(XbbConstant.R_P).append(BiMathEnum.MULTIPLY.getArithmetic()).append(BiConstant.COUNT_ALISA).toString();
            }
        }
        return outSql;
    }

    /**
     * 判断公式内是否包含COUNT或者COUNT_DISTINCT
     *
     * @param formulaStr 计算公式
     * @author zyy
     * @date 2023/2/20
     * @version v1.0
     */
    public boolean countFlag(String formulaStr) {
        return StringUtils.isBlank(formulaStr) ? Boolean.FALSE :
                ((formulaStr.contains(XbbConstant.COUNT) || XbbConstant.COUNTD.equals(formulaStr)) ? Boolean.TRUE : Boolean.FALSE);
    }

    /**
     * 添加all_count的SQL语句
     *
     * @param aggFieldsList  汇总字段sql数组
     * @param fieldAttrPojo 字段实体
     * @param sumTotalFieldsList 汇总字段sql数组（统计表使用）
     * @author zyy
     * @date 2023/2/21
     * @version v1.0
     */
    private void insertAlCountSql(List<String> aggFieldsList, FieldAttrPojo fieldAttrPojo, List<String> sumTotalFieldsList,Integer chartType) {
        //子查询内层SQL固定一个all_count语句 (整个图表渲染期间只存一次即可)
        String allCountSql;
        if(ChartTypeEnum.DATA_QUERY_TABLE.getCode() == chartType){
            allCountSql = BasicConstant.ONE_STRING + XbbConstant.AS + BiConstant.COUNT_ALISA;
        }else{
            allCountSql = "COUNT(*)" + XbbConstant.AS + BiConstant.COUNT_ALISA;
        }
        if(!aggFieldsList.contains(allCountSql)){
            aggFieldsList.add(allCountSql);
        }
        if(!sumTotalFieldsList.contains(allCountSql) && AdvancedComputingFirstEnum.noneYearChain(fieldAttrPojo.getAdvancedComputing())){
            sumTotalFieldsList.add(allCountSql);
        }
    }

    /**
     * 拆解计算公式
     *
     * @param formula 完整的公式
     * @param formulaName 公式最外层的名称
     * @param aggType  汇总类型
     * @return boolean 拆解标记   true-拆解  false-未拆解
     * @author zyy
     * @date 2023/1/10
     * @version v1.0
     */
    private boolean splitFormula(List<String> splitResultList, String formula, String formulaName, AggTypeEnum aggType) {
        boolean flag;
        // if,max，min，日期恒不拆解
        // avg 聚合方式时拆解、非聚合方式不拆解
        if(BiMathEnum.IF.getFormula().equals(formulaName)
                || BiMathEnum.MAX.getFormula().equals(formulaName)
                || BiMathEnum.MIN.getFormula().equals(formulaName)
                || BiMathEnum.DATE_DIFF.getFormula().equals(formulaName)
                || (BiMathEnum.AVERAGE.getFormula().equals(formulaName) && !AggTypeEnum.isAggType(aggType))){
            splitResultList.add(formula);
            flag = false;
        }else {
            analysisFormula(splitResultList,formula);
            flag = true;
        }
        return flag;
    }

    /**
     * 组装汇总字段的数据
     *
     * @param aggType 汇总类型
     * @param side 轴方向
     * @param attr 汇总字段的attr
     * @param aggFieldMap 汇总字段映射容器
     * @param fieldAttrPojo 汇总字段实体
     * @param sortMap 排序字段映射容器
     * @author zyy
     * @date 2022/10/8
     * @version v1.0
     */
    public void fieldInduction(AggTypeEnum aggType, String side, String attr, Map<String, AggFieldPojo> aggFieldMap, FieldAttrPojo fieldAttrPojo, Map<String, String> sortMap) {
        AggFieldPojo aggFieldPojo = new AggFieldPojo();
        aggFieldPojo.setAggTypeEnum(aggType);
        aggFieldPojo.setAttr(attr);
        aggFieldPojo.setFormId(fieldAttrPojo.getFormId());
        Set<String> keySet = aggFieldMap.keySet();
        int index = 0;
        if(!keySet.isEmpty()){
            index = index + keySet.size();
        }
        aggFieldPojo.setIndex(index);
        aggFieldPojo.setAxis(side);
        aggFieldPojo.setLabelName(fieldAttrPojo.getLabelName());
        aggFieldPojo.setFieldType(fieldAttrPojo.getFieldType());
        //高级计算方式
        aggFieldPojo.setAdvancedComputing(fieldAttrPojo.getAdvancedComputing());
        aggFieldMap.put(attr,aggFieldPojo);
        // 排序
        String sortAttr = sortMap.get(attr);
        if(StringUtil.isNotEmpty(sortAttr) && (Objects.equals(sortAttr.trim(),"asc") || Objects.equals(sortAttr.trim(),"desc"))){
            sortMap.put(attr,attr + " " + sortAttr);
        }
    }

    public void addAggComputedField(ChartEntity chartEntity,HashMap<String, Set<String>> selectItemMap, String side, Map<Long, String> formIdTableMap, Map<String, AggFieldPojo> aggFieldMap, List<String> aggFieldsList, FieldAttrPojo fieldAttrPojo, AggTypeEnum aggType, String attr, Map<String, String> sortMap,List<String> sumTotalFieldsList,List<String> subQuerySqlOfCountList,List<String> summaryColumnAlisa) throws XbbException {
        JSONObject formulaInfo = fieldAttrPojo.getFormulaInfo();
        String formula = formulaInfo.getString("formula");
        Boolean countFlag = formulaInfo.getBoolean("countFlag");
        try {
            if(Objects.nonNull(countFlag) && countFlag){
                buildComputeSqlOfCount(chartEntity,formulaInfo,fieldAttrPojo,formIdTableMap,aggFieldsList,AggTypeEnum.SUM,sumTotalFieldsList,selectItemMap,subQuerySqlOfCountList);
            }else{
                formula = getString(formulaInfo,selectItemMap,formula, formIdTableMap);
                String attrFormulaSql = "";
                if(Objects.equals(ChartTypeEnum.DATA_QUERY_TABLE.getCode(),chartEntity.getChartType())){
                    //查询表不需要嵌套汇总方式
                    attrFormulaSql = XbbConstant.L_P +formula + XbbConstant.R_P + " AS " + attr;
                }else{
                    aggType = AggTypeEnum.SUM;
                    attrFormulaSql = aggType.getAlias()+ XbbConstant.L_P +formula + XbbConstant.R_P + " AS " + attr;
                }
                //TODO 计算字段的汇总方式目前横为sum   2021年12月22日11:06:05
                aggFieldsList.add(attrFormulaSql);
                JSONArray advancedComputing = fieldAttrPojo.getAdvancedComputing();
                if (AdvancedComputingFirstEnum.noneYearChain(advancedComputing)) {
                    sumTotalFieldsList.add(attrFormulaSql);
                }
                summaryColumnAlisa.add(attr);
            }
            //抽取共有逻辑
            fieldInduction(aggType,side,attr,aggFieldMap,fieldAttrPojo,sortMap);
        } catch (XbbException e) {
            logger.error("计算字段的公式格式有问题", e);
            throw e;
        }catch (Exception e){
            logger.error("计算字段的公式有误===", e);
            throw new XbbException(BiErrorCodeEnum.API_ERROR_241017);
        }
    }

    /**
     * 查询表汇总sql的返回字段sql处理
     *
     *
     * @param selectFiledList selectFieldList会在不同情况下代表的具体值：子查询字段sql、正常字段sql
     * @return java.lang.String
     * @author zyy
     * @date 2023/4/17
     * @version v1.0
     */
    public String dealDataQueryAggFieldSql(List<String> selectFiledList) {
        StringBuilder stringBuilder = new StringBuilder();
        List<String> outSqlList = new ArrayList<>();
        for (String str : selectFiledList) {
            String sqlPre = str;
            String sqlAlisa = str;
            if(str.contains(XbbConstant.AS)){
                String[] sqlSplit = str.split(XbbConstant.AS);
                sqlPre = sqlSplit[0];
                sqlAlisa = sqlSplit[1];
            }
            if(!containsAggFunction(sqlPre)){
                sqlPre = getFormulaByAggType(AggTypeEnum.SUM, sqlPre,true);
            }
            outSqlList.add(sqlPre + XbbConstant.AS + sqlAlisa);
        }
        stringBuilder.append(String.join(XbbConstant.SQL_COMMA,outSqlList));
        return stringBuilder.toString();
    }

    private boolean containsAggFunction(String sqlPre) {
        //判断当前是否已经存在聚合函数，防止聚合函数嵌套报错
        for (AggTypeEnum aggTypeEnum : AggTypeEnum.values()) {
            if(sqlPre.contains(aggTypeEnum.getAlias() + XbbConstant.L_P) || sqlPre.contains(aggTypeEnum.getAlias().toUpperCase() + XbbConstant.L_P)){
                return true;
            }
        }
        return false;
    }
}
